---
description: Model one-to-one relationships in Hasura
keywords:
  - hasura
  - docs
  - schema
  - relationship
  - one-to-one
  - 1-1
sidebar_position: 2
sidebar_label: One-to-one relationship
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';

# Modeling One-to-One Table Relationships

## Introduction

A `one-to-one` relationship between two tables can be established via a **unique foreign key constraint**.

Say we have the following two tables in our database schema:

```sql
authors (
  id SERIAL PRIMARY KEY,
  name TEXT
)

passport_info (
  id SERIAL PRIMARY KEY,
  owner_id INT NOT NULL
  passport_number TEXT
  ...
)
```

These two tables are related via a `one-to-one` relationship. i.e.:

- an `author` can have one `passport_info`
- a `passport_info` has one `owner`

## Step 1: Set up a table relationship in the database

This `one-to-one` relationship can be established in the database by:

1.  Adding a **foreign key constraint** from the `passport_info` table to the `authors` table using the `owner_id` and
    `id` columns of the tables respectively
2.  Adding a **unique constraint** to the `owner_id` column for the `passport_info` table

This will ensure that the value of the `owner_id` column in `passport_info` table is present in the `id` column of the
`authors` table and there will be only one row with a particular `owner_id`.

## Step 2: Set up GraphQL relationships

To access the nested objects via the GraphQL API,
[create the following relationships](/schema/postgres/table-relationships/create.mdx):

- Object relationship, `passport_info` from the `authors` table using `id -> passport_info :: owner_id`
- Object relationship, `owner` from the `passport_info` table using `owner_id -> authors :: id`

## Query using one-to-one relationships

We can now:

- fetch a list of `authors` with their `passport_info`:

<GraphiQLIDE
  query={`query {
  authors {
    id
    name
    passport_info {
      id
      passport_number
    }
  }
 }`}
  response={`{
  "data": {
    "authors": [
      {
        "id": 1,
        "name": "Justin",
        "passport_info": {
          "id": 1,
          "passport_number": "987456234"
        }
      },
      {
        "id": 2,
        "name": "Beltran",
        "passport_info": {
          "id": 2,
          "passport_number": "F0004586"
        }
      }
    ]
  }
 }`}
/>

- fetch a list of `passport_infos` with their `owner`:

<GraphiQLIDE
  query={`query {
  passport_info {
    id
    passport_number
    owner {
      id
      name
    }
  }
 }`}
  response={`{
  "data": {
    "passport_info": [
      {
        "id": 1,
        "passport_number": "987456234",
        "owner": {
          "id": 1,
          "name": "Justin"
        }
      },
      {
        "id": 2,
        "passport_number": "F0004586",
        "owner": {
          "id": 2,
          "name": "Beltran"
        }
      }
    ]
  }
 }`}
/>

## Insert using one-to-one relationships {#one-to-one-insert}

We can now:

- insert `passport_info` with their `owner` where the `owner` might already exist (assume unique `name` for `owner`):

<GraphiQLIDE
  query={`mutation upsertPassportInfoWithOwner {
  insert_passport_info(objects: [
    {
      passport_number: "X98973765",
      owner: {
        data: {
          name: "Kelly"
        },
        on_conflict: {
          constraint: owner_name_key,
          update_columns: [name]
        }
      },
    }
  ]) {
    returning {
      passport_number
      owner {
        name
      }
    }
  }
 }`}
  response={`{
  "data": {
    "insert_passport_info": {
      "returning": [
        {
          "passport_number": "X98973765",
          "owner": {
            "name": "Kelly"
          }
        }
      ]
    }
  }
 }`}
/>

:::info Note

You can avoid the `on_conflict` clause if you will never have conflicts.

:::

### Caveat for nested inserts

Due to the way nested inserts are typically handled (described
[here](/mutations/postgres/insert.mdx#pg-nested-inserts)), the order of object insertion needs to be specified using the
[insertion_order](/api-reference/syntax-defs.mdx#objrelusingmanualmapping) option while creating one-to-one
relationships via the API. This is necessary to ensure nested inserts are possible using either side as the parent which
would otherwise error out with a `Not-NULL violation` error in one of the cases.

In our example, inserting a `passport_info` with their nested `owner` will work seamlessly but trying to insert an
`author` with their nested `passport_info` will throw a constraint violation error in case the insertion order is not
specified for the `owner` object relationship.
